﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Data.SqlClient;
using System.Threading;
public partial class _Default : System.Web.UI.Page
{
    _dbDataContext _db = new _dbDataContext();
    public int count = 0;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindDrop();
        }
    }
    protected DataTable Get_dt_import(string SheetName)
    {
        DataTable tb = PageURL.ReadFile_Excel(Server.MapPath("~/ThongKe/" + Session["tenfiledanhsach"].ToString()), SheetName, Path.GetExtension(file.FileName).Trim());
        DataTable tb1 = new DataTable();
        //add so cot bang voi so cot cua tb
        for (int i = 0; i < tb.Columns.Count; i++)
        {
            tb1.Columns.Add("cols" + i.ToString());
        }
        //DataRow dr;
        //lay tu dong so 4
        for (int i = 4; i < tb.Rows.Count; i++)
        {
            //dr = tb.Rows[i];
            //tb1.Rows.Add(dr.ItemArray);
            DataRow dr = tb1.NewRow();
            for (int j = 0; j < 4; j++)
            {
                dr[j] = tb.Rows[i][j].ToString();
            }
            try
            {
                string n1 = tb.Rows[i][4].ToString();
                dr[4] = n1.Split('/')[1] + "/" + n1.Split('/')[0] + "/" + n1.Split('/')[2];
            }
            catch { dr[4] = ""; }
            try
            {
                string n2 = tb.Rows[i][4].ToString();
                dr[5] = n2.Split('/')[1] + "/" + n2.Split('/')[0] + "/" + n2.Split('/')[2];
            }
            catch { dr[5] = ""; }
            for (int k = 6; k < tb.Columns.Count; k++)
            {
                dr[k] = tb.Rows[i][k].ToString();
            }
            tb1.Rows.Add(dr);
        }
        return tb1;
    }
    //import data from excel to datatable and binding into gridview to show
    public void import_data(string Sheet_name)
    {
        try
        {
            string tenfiledoanvien = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString() + Path.GetExtension(file.FileName);
            Session["tenfiledanhsach"] = tenfiledoanvien;
            file.SaveAs(Server.MapPath(PageURL.local + @"ThongKe\" + tenfiledoanvien));
            DataTable tb = PageURL.ReadFile_Excel(Server.MapPath("~/ThongKe/" + tenfiledoanvien), Sheet_name, Path.GetExtension(file.FileName).Trim());
            DataTable tb1 = new DataTable();
            //add so cot bang voi so cot cua tb
            for (int i = 0; i < tb.Columns.Count; i++)
            {
                tb1.Columns.Add("cols"+i.ToString());
            }
            //DataRow dr;
            //lay tu dong so 4
            for (int i = 4; i < tb.Rows.Count; i++)
            {
                //dr = tb.Rows[i];
                //tb1.Rows.Add(dr.ItemArray);
                DataRow dr = tb1.NewRow();
                for (int j = 0; j < 4; j++)
                {
                    dr[j] = tb.Rows[i][j].ToString();
                }
                try
                {
                    string n1 = tb.Rows[i][4].ToString();
                    dr[4] = n1.Split('/')[1] + "/" + n1.Split('/')[0] + "/" + n1.Split('/')[2];
                }
                catch { dr[4] = "";  }
                try
                {
                    string n2 = tb.Rows[i][4].ToString();
                    dr[5] = n2.Split('/')[1] + "/" + n2.Split('/')[0] + "/" + n2.Split('/')[2];
                }
                catch { dr[5] = ""; }
                for (int k = 6; k < tb.Columns.Count; k++)
                {
                    dr[k] = tb.Rows[i][k].ToString();
                }
                tb1.Rows.Add(dr);
            }
            grid.DataSource = tb1;
            grid.DataBind();
            lblerror.Text = "";
            if (tb1.Rows.Count != 0)
            {
                pndetail.Visible = true;
                grid.Visible = true;
                lbltrangthai.Text = "";
            }
            else
            {
                pndetail.Visible = false;
            }
        }
        catch
        {
            lblerror.ForeColor = System.Drawing.Color.Red;
            lblerror.Text = "<i class=' icon-frown orange' ></i> Chuẩn đoán lỗi: Sai tên bảng hoặc sai định dạng file";
        }

    }
    protected void chkselectAll_CheckedChanged(object sender, EventArgs e)
    {
        CheckBox chk = (CheckBox)sender;
        CheckBox chkitem;
        foreach (GridViewRow dr in grid.Rows)
        {
            chkitem = (CheckBox)dr.FindControl("chkchon");
            if (chk.Checked == true)
            {
                chkitem.Checked = true;
            }
            else
            {
                chkitem.Checked = false;
            }
        }
    }
    protected void grid_RowCreated(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowIndex == 0)
        {
            e.Row.Cells[0].RowSpan = 2;
            e.Row.Cells[1].RowSpan = 2;
            e.Row.Cells[4].RowSpan = 2;
            e.Row.Cells[5].RowSpan = 2;
            e.Row.Cells[6].RowSpan = 2;
            e.Row.Cells[7].RowSpan = 2;
            e.Row.Cells[15].RowSpan = 2;
            e.Row.Cells[16].RowSpan = 2;
            e.Row.Cells[17].RowSpan = 2;


            e.Row.Cells[2].ColumnSpan = 2;
            e.Row.Cells[8].ColumnSpan = 2;
            e.Row.Cells[10].ColumnSpan = 5;
            e.Row.Cells[3].Visible = false;
            e.Row.Cells[9].Visible = false;
            e.Row.Cells[11].Visible = false;
            e.Row.Cells[12].Visible = false;
            e.Row.Cells[13].Visible = false;
            e.Row.Cells[14].Visible = false;
            e.Row.Font.Bold = true;
            //grid.Rows[0].Cells[0].HorizontalAlign= HorizontalAlign.Center;
        }
        if (e.Row.RowIndex == 1)
        {
            e.Row.Cells[0].Visible = false;
            e.Row.Cells[1].Visible = false;
            e.Row.Cells[4].Visible = false;
            e.Row.Cells[5].Visible = false;
            e.Row.Cells[6].Visible = false;
            e.Row.Cells[7].Visible = false;
            e.Row.Cells[15].Visible = false;
            e.Row.Cells[16].Visible = false;
            e.Row.Cells[17].Visible = false;
            e.Row.Font.Bold = true;
        }
        e.Row.Cells[0].HorizontalAlign = HorizontalAlign.Center;
    }
    protected void grid_DataBound(object sender, EventArgs e)
    {
        try
        {
            grid.Rows[0].HorizontalAlign = HorizontalAlign.Center;
            grid.Rows[2].HorizontalAlign = HorizontalAlign.Center;
            grid.Rows[2].Font.Italic = true;
        }
        catch { }
   }
    List<string> listrowindex = new List<string>();
    private void ProcessData()
    {
        listrowindex.Clear();
        tblDoanVien _tb;
        tblQuanHeXaHoi _tbxahoi;
        string madoanvien = "";
        var listtrinhdochuyenmon = _db.tblDanhMucs.Where(r => r.Loai == 1016).ToList();
        var listngoaingu = _db.tblDanhMucs.Where(r => r.Loai == 4).ToList();
        var listtinhoc = _db.tblDanhMucs.Where(r => r.Loai == 5).ToList();
        var listchucvudoan = _db.tblDanhMucs.Where(r => r.Loai == 9).ToList();
        var listlyluanchinhtri = _db.tblDanhMucs.Where(r => r.Loai == 7).ToList();
        DataTable tb = Get_dt_import(txtsheetname.Text);
        //.Threading.Thread.Sleep(5000);
        string strmadoanvien = "";
        string donvi = drldonvi.SelectedValue.ToString(); //MemberShip_ASP.GetMaDonViFromUserName(this.Page.User.Identity.Name);
        try { strmadoanvien = donvi.Trim().Substring(4); }
        catch {}
        for (int i = 3; i < tb.Rows.Count; i++)
        {
            _tb = new tblDoanVien();
            madoanvien = strmadoanvien + string.Format("{0:00000000}", CatalogAccess.GetLastMaDoanVienCuaDonVi(donvi));
            _tb.MaDonVien = madoanvien;
            _tb.HoTen = tb.Rows[i][1].ToString();
            _tb.Phai = (!string.IsNullOrEmpty(tb.Rows[i][2].ToString()) ? true : false);
            if(CatalogAccess.ConvertToDateBool(tb.Rows[i][4].ToString()))
            _tb.NgaySinh = CatalogAccess.ConvertToDate(tb.Rows[i][4].ToString());
            if (CatalogAccess.ConvertToDateBool(tb.Rows[i][5].ToString()))
            {
                _tb.NgayVaoDoan = CatalogAccess.ConvertToDate(tb.Rows[i][5].ToString());
            }
            _tb.NguyenQuan = tb.Rows[i][6].ToString();
            _tb.ChoOHienNay = tb.Rows[i][7].ToString();
            _tb.TrinhDoVanHoa = tb.Rows[i][10].ToString();
            _tb.TrucThuoc = donvi;//MemberShip_ASP.GetMaDonViFromUserName(this.Page.User.Identity.Name);
            _tb.TrangThai = 1;
            _tb.TrangThaiXoa = false;
            _tb.DangVien = false;
            _tb.BangCap = "@ @";
            _tb.DoanVienSinhHoatNoiCuTru = true;
            _tb.SucKhoe = "Tốt";
            _tb.PhatTrienMoi = false;
            _tb.DanToc = 110;
            _tb.TonGiao = 15;
            _tb.HonNhan = 17;
            _tb.NoiSinh = "";
            _tb.CMND = "";
            _tb.NoiCap = "";
            _tb.NguyenQuan = "";
            _tb.HoKhauThuongTru = "";
            _tb.ChoOHienNay = "";
            _tb.DienThoai = "";
            _tb.Email = "";
            _tb.XuatThan = "";
            _tb.NgheNghiep = "";
            _tb.MoTaCongViec = "";
            _tb.DonViCu = "";
            //trinh do chuyen mon
            string trinhdochuyenmon = "";
            try
            {
                trinhdochuyenmon = listtrinhdochuyenmon.Single(r => r.TDMKhongDau.Trim() == SecurityLib.ToolsMore.ConvertToUnSignMutile(tb.Rows[i][12].ToString()).Trim().ToUpper().Replace("-"," ")).ID.ToString();
                _tb.TrinhDoChuyenMonID = int.Parse(trinhdochuyenmon);
            }
            catch
            {
                if (tb.Rows[i][12].ToString() != "")
                {
                    tblDanhMuc _tbl = new tblDanhMuc();
                    _tbl.TenDanhMuc = tb.Rows[i][12].ToString();
                    _tbl.TDMKhongDau = SecurityLib.ToolsMore.ConvertToUnSignMutile(tb.Rows[i][12].ToString()).ToUpper().Replace("-", " ");
                    _tbl.Loai = 1016;
                    _db.tblDanhMucs.InsertOnSubmit(_tbl);
                    _db.SubmitChanges();
                    listtrinhdochuyenmon = _db.tblDanhMucs.Where(r => r.Loai == 1016).ToList();
                    trinhdochuyenmon = listtrinhdochuyenmon.Single(r => r.TDMKhongDau.Trim() == SecurityLib.ToolsMore.ConvertToUnSignMutile(tb.Rows[i][12].ToString()).Trim().ToUpper().Replace("-"," ")).ID.ToString();
                    _tb.TrinhDoChuyenMonID = int.Parse(trinhdochuyenmon);
                }
            }
            //trinh do ngoai ngu
            string trinhdongoaingu = "";
            try
            {
                trinhdongoaingu = listngoaingu.Single(r => r.TDMKhongDau.Trim() == SecurityLib.ToolsMore.ConvertToUnSignMutile(tb.Rows[i][13].ToString().Trim()).ToUpper().Replace("-"," ")).ID.ToString();
                _tb.NgoaiNgu = int.Parse(trinhdongoaingu);
            }
            catch
            {
                if (tb.Rows[i][13].ToString() != "")
                {
                    tblDanhMuc _tbl = new tblDanhMuc();
                    _tbl.TenDanhMuc = tb.Rows[i][13].ToString();
                    _tbl.TDMKhongDau = SecurityLib.ToolsMore.ConvertToUnSignMutile(tb.Rows[i][13].ToString()).ToUpper().Replace("-", " ");
                    _tbl.Loai = 4;
                    _db.tblDanhMucs.InsertOnSubmit(_tbl);
                    _db.SubmitChanges();
                    listngoaingu = _db.tblDanhMucs.Where(r => r.Loai == 4).ToList();
                    trinhdongoaingu = listngoaingu.Single(r => r.TDMKhongDau.Trim() == SecurityLib.ToolsMore.ConvertToUnSignMutile(tb.Rows[i][13].ToString().Trim()).ToUpper().Replace("-", " ")).ID.ToString();
                    _tb.NgoaiNgu = int.Parse(trinhdongoaingu);
                }
            }

            //trinh do tin hoc
            string trinhdotinhoc = "";
            try
            {
                trinhdotinhoc = listtinhoc.Single(r => r.TDMKhongDau.Trim() == SecurityLib.ToolsMore.ConvertToUnSignMutile(tb.Rows[i][14].ToString().Trim()).ToUpper().Replace("-", " ")).ID.ToString();
                _tb.TinHoc = int.Parse(trinhdotinhoc);
            }
            catch
            {
                if (tb.Rows[i][14].ToString() != "")
                {
                    tblDanhMuc _tbl = new tblDanhMuc();
                    _tbl.TenDanhMuc = tb.Rows[i][14].ToString();
                    _tbl.TDMKhongDau = SecurityLib.ToolsMore.ConvertToUnSignMutile(tb.Rows[i][14].ToString()).ToUpper().Replace("-", " ");
                    _tbl.Loai = 5;
                    _db.tblDanhMucs.InsertOnSubmit(_tbl);
                    _db.SubmitChanges();
                    listtinhoc = _db.tblDanhMucs.Where(r => r.Loai == 5).ToList();
                    trinhdotinhoc = listtinhoc.Single(r => r.TDMKhongDau.Trim() == SecurityLib.ToolsMore.ConvertToUnSignMutile(tb.Rows[i][14].ToString().Trim()).ToUpper().Replace("-", " ")).ID.ToString();
                    _tb.TinHoc = int.Parse(trinhdotinhoc);
                }
            }
            _tb.ChucVuNoiCongTac = tb.Rows[i][15].ToString();
            //chuc vu doan
            string chucvudoan = "";
            try
            {
                chucvudoan = listchucvudoan.Single(r => r.TDMKhongDau.Trim() == SecurityLib.ToolsMore.ConvertToUnSignMutile(tb.Rows[i][16].ToString().Trim()).ToUpper().Replace("-", " ")).ID.ToString();
            }
            catch { }
            if (chucvudoan != "")
            {
                _tb.ChucVuDoanID = int.Parse(chucvudoan);
            }
            else
            {
                _tb.ChucVuDoanID = 109;
            }
            try
            {
                _db.tblDoanViens.InsertOnSubmit(_tb);
                _db.SubmitChanges();
                //dem so dong thuc hien thanh cong
                count++;
                //dong xoa duoc vao list
                listrowindex.Add(tb.Rows[i][0].ToString());
                //them doan vien nay vao bang thong tin khac cua doan vien
                tblThongTinKhac _table = new tblThongTinKhac();
                _table.MaDoanVien = madoanvien;
                try
                {
                    _db.tblThongTinKhacs.InsertOnSubmit(_table);
                    _db.SubmitChanges();
                }
                catch { }
                //them doan vien nay vao kinh te gia dinh
                tblKinhTeGiaDinh _kinhte = new tblKinhTeGiaDinh();
                _kinhte.MaDoanVien = madoanvien;
                _kinhte.LoaiNhaThueCap = -1;
                _kinhte.LoaiNhaXayMua = -1;
                _kinhte.NhaCap = false;
                _kinhte.NhaXay = false;
                _kinhte.LuongNam = "";
                _kinhte.NguonKhac = "";
                _kinhte.QuanTrinhLuong = "";
                _kinhte.DienTichThueCap = "";
                _kinhte.TongDienTichXayMua = "";
                _kinhte.DatSanXuatKinhDoanh = "";
                _kinhte.DatTuMua = "";
                _kinhte.DatDuocCap = "";
                try
                {
                    _db.tblKinhTeGiaDinhs.InsertOnSubmit(_kinhte);
                    _db.SubmitChanges();
                }
                catch { }
                //tham vao bang quan he xa hoi
                _tbxahoi = new tblQuanHeXaHoi();
                _tbxahoi.MaDoanVien = madoanvien;
                if (tb.Rows[i][8].ToString() != "")
                {
                    if (CatalogAccess.ConvertToDateBool(tb.Rows[i][8].ToString()))
                    {
                        _tbxahoi.NgayVaoDangDuBi = CatalogAccess.ConvertToDate(tb.Rows[i][8].ToString());
                    }
                    if (tb.Rows[i][9].ToString() != "")
                    {
                        if (CatalogAccess.ConvertToDateBool(tb.Rows[i][9].ToString()))
                        {
                            _tbxahoi.NgayVaoDangChinhThuc = CatalogAccess.ConvertToDate(tb.Rows[i][9].ToString());
                        }
                    }
                }
                //ly luan chinh tri
                if (tb.Rows[i][11].ToString() != "")
                {
                    string lyluan = "";
                    try
                    {
                        lyluan = listlyluanchinhtri.Single(r => r.TDMKhongDau.Trim() == SecurityLib.ToolsMore.ConvertToUnSignMutile(tb.Rows[i][11].ToString().Trim()).ToUpper().Replace("-", " ")).ID.ToString();
                    }
                    catch { }
                    if (lyluan != "")
                    {
                        _tbxahoi.LyLuanChinhTriID = int.Parse(lyluan);
                    }
                }
                try
                {
                    if (tb.Rows[i][8].ToString() != "" || tb.Rows[i][11].ToString() != "")
                    {
                        _db.tblQuanHeXaHois.InsertOnSubmit(_tbxahoi);
                        _db.SubmitChanges();
                    }
                }
                catch { }
            }
            catch { }
        }
    }
    protected void BindDrop()
    {
        ListItem item;
        item = new ListItem("---Chọn Đơn vị---", "0");
        drldonvi.Items.Add(item);
        _dbDataContext data = new _dbDataContext();
        string madonvi = MemberShip_ASP.GetMaDonViFromUserName(Page.User.Identity.Name);//lay ma don vi thuoc username dang nhap
        var d = from p in data.tblCoSoDoans where ((p.TrucThuoc.Trim() == madonvi.Trim() || p.MaDonVi.Trim() == madonvi) && p.GiaiThe == false) select p;
        foreach (var r in d)
        {
            item = new ListItem(r.TenDonVi.Trim(), r.MaDonVi.Trim());
            drldonvi.Items.Add(item);
        }
    }

    protected void btnImport_Click(object sender, EventArgs e)
    {
        if (drldonvi.SelectedValue == "0")
        {
            lblthongbao.Text = "Chưa chọn đơn vị.";
        }
        else
        {
            if (Session["tenfiledanhsach"] != null)
            {
                ProcessData();
                lbltrangthai.ForeColor = System.Drawing.Color.Red;
                lbltrangthai.Font.Italic = true;
                lbltrangthai.Text = "<i class='icon-ok green'></i> Tải lên thành công " + count.ToString() + " đoàn viên";
                //xoa cac dong da chen thanh cong trong tb
                DataRow[] dr;
                DataTable tb = Get_dt_import(txtsheetname.Text);
                foreach (string item in listrowindex) //listrowindex: danh sach cac so thu tu da chen thanh cong
                {
                    try
                    {
                        dr = tb.Select("cols0='" + item + "'");
                        foreach (DataRow dr1 in dr)
                        {
                            tb.Rows.Remove(dr1);
                        }
                    }
                    catch { }
                }
                grid.DataSource = tb;
                grid.DataBind();
                if (grid.Rows.Count <= 2)
                {
                    grid.Visible = false;
                }
                try
                {
                    System.IO.File.Delete(Server.MapPath(PageURL.local + @"ThongKe\" + Session["tenfiledanhsach"].ToString()));
                }
                catch { }
                Session.Remove("tenfiledanhsach");
            }
            else
            {
                lbltrangthai.Text = "Không có danh sách.";
            }
        }
    }
    protected void btn_Click(object sender, EventArgs e)
    {
        if (txtsheetname.Text == "")
        {
            lblerror.ForeColor = System.Drawing.Color.Red;
            lblerror.Text = "<i class='icon-remove red' ></i> Trường nhập rỗng ";

        }
        else
        {
            if (file.HasFile)
            {
                import_data(txtsheetname.Text.Trim());
            }
            else
            {
                grid.DataSource = null;
                pndetail.Visible = false;
                lblerror.ForeColor = System.Drawing.Color.Red;
                lblerror.Text = "<i class='icon-hand-left orange ' ></i> Chọn file tải lên ";
            }
        }
    }
}
